Module modCustomer

    Private isError As Boolean

    ' Company Default Structure
    Public Structure Customer
        Public DBCode As String
        Public CardCode As String
        Public CardName As String
        Public ContactPerson As String
        Public BillToAddress1 As String
        Public BillToAddress2 As String
        Public BillToAddress3 As String
        Public BillToAddress4 As String
        Public BillToAddress As String
        Public Country As String
        Public ZipCode As String
        Public Phone1 As String
        Public Fax As String
        Public EmailAddress As String
        Public Website As String
        Public TaxRegistration As String
        Public PaymentTerm As String
        Public SaleManCode As String
        Public ARControlAccount As String
        Public TaxCode As String
        Public CreditLimit As Double
        Public Discount As Double
        Public PaymentMethod As String
        Public ShipAddress1 As String
        Public ShipAddress2 As String
        Public ShipAddress3 As String
        Public ShipAddress4 As String
        Public ShipToAddress As String
        Public ShipCountry As String
        Public ShipPostalCode As String
        Public CurrencyCode As String
        Public CustomerType As String
        Public Remarks As String

    End Structure

    Public oCustDef As Customer
    Private InputFolderPath As String = p_oCompDef.sInboxDir

    Public Function UploadCUSTOMER(ByVal sSubFolder As String, ByRef sErrdesc As String) As Long

        'Event      :   UploadCUSTOMER()
        'Purpose    :   For Checking Errors & updation of data in SAP from Customer CSV Files
        'Author     :   Sri
        'Date       :   20 Nov 2013   

        Dim sFuncName As String = String.Empty
        Dim BPFileExist As Boolean
        Dim sFileType As String = String.Empty

        Try
            sFuncName = "UploadCUSTOMER"
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)
            BPFileExist = False

            Dim DirInfo As New System.IO.DirectoryInfo(p_oCompDef.sInboxDir & "\" & sSubFolder)
            Dim files() As System.IO.FileInfo

            files = DirInfo.GetFiles("*CUSTOMER*.csv")

            For Each File As System.IO.FileInfo In files
                BPFileExist = True
                isError = False
                sFileType = Replace(File.Name, ".csv", "").Trim

                If Right(sFileType, 3) = "SUB" Then
                    If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling ProceessCustomer_SUB for " & File.FullName, sFuncName)
                    If ProceessCustomer_SUB(File.FullName, sErrdesc) <> RTN_SUCCESS Then
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Error - Calling FileMoveToArchive for moving CSV file to archive folder", sFuncName)
                        FileMoveToArchive(File, File.FullName, RTN_ERROR)
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Error in updation. RollBack executed for " & File.FullName, sFuncName)
                        AddDataToTable(p_oDtError, File.Name, "Error", sErrdesc)
                    Else
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Data updated successfully for " & File.FullName, sFuncName)
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling FileMoveToArchive for moving CSV file to archive folder", sFuncName)
                        FileMoveToArchive(File, File.FullName, RTN_SUCCESS)
                    End If
                Else
                    If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling readCSVFileCustomer for " & File.FullName & " to check error in CSV file", sFuncName)
                    readCSVFileCustomer(File.FullName, File.Name)
                    If isError = True Then
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Wrong data in CSV file " & File.FullName, sFuncName)
                        WriteToLogFile("Wrong data in CSV file " & File.FullName, sFuncName)
                        AddDataToTable(p_oDtError, File.Name, "Error", "Invalid data in CSV file. Please refer to the Error log file for more details.")
                        FileMoveToArchive(File, File.FullName, RTN_ERROR)
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Error in updation. RollBack executed for " & File.FullName, sFuncName)
                    Else
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling AddUpdateBP for " & File.FullName & " to update data in database", sFuncName)
                        If AddUpdateCustomer(File.FullName, sErrdesc) <> RTN_SUCCESS Then
                            If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling FileMoveToArchive for moving CSV file to archive folder", sFuncName)
                            AddDataToTable(p_oDtError, File.Name, "Error", sErrdesc)
                            FileMoveToArchive(File, File.FullName, RTN_ERROR)
                            If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Error in updation. RollBack executed for " & File.FullName, sFuncName)
                        Else
                            If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Data updated successfully for " & File.FullName, sFuncName)
                            If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling FileMoveToArchive for moving CSV file to archive folder", sFuncName)
                            FileMoveToArchive(File, File.FullName, RTN_SUCCESS)
                        End If
                    End If
                End If
            Next
            UploadCUSTOMER = RTN_SUCCESS
            If BPFileExist = False Then If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("No Business Partner CSV available for updation", sFuncName)
        Catch ex As Exception
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error in Uploading BP", sFuncName)
            Call WriteToLogFile(ex.Message, sFuncName)
        End Try
    End Function

    Private Sub readCSVFileCustomer(ByVal CurrFileToUpload As String, ByVal sFileName As String)

        'Event      :   readCSVFileCustomer
        'Purpose    :   For reading of Business Partner CSV file
        'Author     :   Sri 
        'Date       :   20 NOV 2013 

        Dim sFuncName As String = ""
        Dim sSQL As String = String.Empty
        Dim dt As DataTable
        
        Try
            sFuncName = "readCSVFileCustomer"
            Dim dv As DataView = GetDataViewFromCSV(CurrFileToUpload)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function...", sFuncName)

            sSQL = "SELECT ""Code"" FROM ""@AI_TB01_COMPANYDATA"""
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
            dt = ExecuteSQLQueryForDT(sSQL)

            For i As Integer = 0 To dv.Count - 1
                With oCustDef
                    .DBCode = dv(i)(0).ToString
                    .CardCode = dv(i)(1).ToString
                    .CardName = dv(i)(2).ToString
                    .ContactPerson = dv(i)(3).ToString
                    .BillToAddress1 = dv(i)(4).ToString
                    .BillToAddress2 = dv(i)(5).ToString
                    .BillToAddress3 = dv(i)(6).ToString
                    .BillToAddress4 = dv(i)(7).ToString
                    .Country = dv(i)(8).ToString
                    .ZipCode = dv(i)(9).ToString
                    .Phone1 = dv(i)(10).ToString
                    .Fax = dv(i)(11).ToString
                    .EmailAddress = dv(i)(12).ToString
                    .Website = dv(i)(13).ToString
                    .TaxRegistration = dv(i)(14).ToString
                    .PaymentTerm = dv(i)(15).ToString
                    .SaleManCode = dv(i)(16).ToString
                    .ARControlAccount = dv(i)(17).ToString
                    .TaxCode = dv(i)(18).ToString
                    .CreditLimit = CDbl(dv(i)(19))
                    .Discount = CDbl(dv(i)(20))
                    .PaymentMethod = dv(i)(21).ToString
                    .ShipAddress1 = dv(i)(22).ToString
                    .ShipAddress2 = dv(i)(23).ToString
                    .ShipAddress3 = dv(i)(24).ToString
                    .ShipAddress4 = dv(i)(25).ToString
                    .ShipCountry = dv(i)(26).ToString
                    .ShipPostalCode = dv(i)(27).ToString
                    .CurrencyCode = dv(i)(28).ToString
                    .CustomerType = dv(i)(29).ToString
                    .Remarks = dv(i)(30).ToString

                    If .DBCode.Length = 0 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Owner entity Code is mandatory", sFuncName)
                    End If

                    If .DBCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Owner entity Code Length is greater then 10", sFuncName)
                    End If

                    dt.DefaultView.RowFilter = "Code = '" & dv(i)(0).ToString & "'"
                    If dt.DefaultView.Count = 0 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Owner enttiy code ::" & dv(i)(0).ToString & " does not exist in SAP [@AI_TB01_COMPANYDATA] table.", sFuncName)
                    End If

                    If .CardCode.Length = 0 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Customer Code is mandatory", sFuncName)
                    End If
                    If .CardCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Customer Code Length is not greater then 15", sFuncName)
                    End If
                    If .CardName.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Customer Name Length is not greater then 80", sFuncName)
                    End If
                    If .ContactPerson.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Contact Person Length is not greater then 80", sFuncName)
                    End If
                    If .BillToAddress1.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Bill To Address1 Length is not greater then 80", sFuncName)
                    End If
                    If .BillToAddress2.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Bill To Address2 Length is not greater then 80", sFuncName)
                    End If
                    If .BillToAddress3.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Bill To Address3 Length is not greater then 80", sFuncName)
                    End If
                    If .BillToAddress4.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Bill To Address4 Length is not greater then 80", sFuncName)
                    End If
                    If .Country.Length > 30 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Country Length is not greater then 30", sFuncName)
                    End If
                    If .ZipCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". ZipCode Length is not greater then 10", sFuncName)
                    End If
                    If .Phone1.Length > 15 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Phone1 Length is not greater then 15", sFuncName)
                    End If
                    If .Fax.Length > 15 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Fax Length is not greater then 15", sFuncName)
                    End If
                    If .EmailAddress.Length > 100 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". EmailAddress Length is not greater then 100", sFuncName)
                    End If
                    If .Website.Length > 100 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Website Length is not greater then 100", sFuncName)
                    End If
                    If .TaxRegistration.Length > 30 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". TaxRegistration Length is not greater then 30", sFuncName)
                    End If
                    If .PaymentTerm.Length > 50 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". PaymentTerm Length is not greater then 50", sFuncName)
                    End If

                    If .SaleManCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". SaleManCode Length is not greater then 10", sFuncName)
                    End If
                    If .ARControlAccount.Length > 15 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". AR Control Account Length is not greater then 15", sFuncName)
                    End If

                    If .TaxCode.Length > 6 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". TaxCode Length is not greater then 6", sFuncName)
                    End If
                    If .PaymentMethod.Length > 50 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Payment Method Length is not greater then 50", sFuncName)
                    End If
                    If .ShipAddress1.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". ShipAddress1 Length is not greater then 80", sFuncName)
                    End If
                    If .ShipAddress2.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". ShipAddress2 Length is not greater then 80", sFuncName)
                    End If
                    If .ShipAddress3.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". ShipAddress3 Length is not greater then 80", sFuncName)
                    End If
                    If .ShipAddress4.Length > 80 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". ShipAddress4 Length is not greater then 80", sFuncName)
                    End If
                    If .ShipPostalCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Ship PostalCode Length is not greater then 10", sFuncName)
                    End If
                    If .CurrencyCode.Length > 3 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Currency Code Length is not greater then 3", sFuncName)
                    End If
                    If .CustomerType.Length > 100 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Customer Type Length is not greater then 100", sFuncName)
                    End If
                    If .Remarks.Length > 255 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & sFileName & ". Remarks Length is not greater then 255", sFuncName)
                    End If

                End With

            Next
        Catch ex As Exception
            isError = True
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error occured while reading content of  " & ex.Message, sFuncName)
            Call WriteToLogFile(ex.Message, sFuncName)
        End Try
    End Sub

    Private Function AddUpdateCustomer(ByVal CurrFileToUpload As String, ByRef sErrdesc As String) As Long

        'Function   :   AddUpdateCustomer()
        'Purpose    :   For Adding new Business Partner
        '           :   iCardType (0 : Customer 1:Vendor)
        'Author     :   Sri
        'Date       :   20 Nov 2013       

        Dim iErr As Integer = 0
        Dim iRet As Integer
        Dim sErr As String = ""
        Dim sSQL As String = ""

        Dim sFuncName As String = "AddUpdateCustomer"
        ' Declare variable
        Dim oBP As SAPbobsCOM.BusinessPartners
        Dim oRs As SAPbobsCOM.Recordset
        Dim oDIComp() As SAPbobsCOM.Company = Nothing
        Dim oDtDistinct As DataTable
        Dim dtPT As DataTable
        Dim dtAC As DataTable

        Try

            oCustDef = Nothing

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("calling GetDataViewFromCSV()", sFuncName)
            Dim dv As DataView = GetDataViewFromCSV(CurrFileToUpload)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("getting distinct DBs in the file.", sFuncName)
            oDtDistinct = dv.Table.DefaultView.ToTable(True, "F1")


            ReDim oDIComp(oDtDistinct.Rows.Count)

            With oCustDef

                For j As Integer = 0 To oDtDistinct.Rows.Count - 1
                    oDIComp(j) = New SAPbobsCOM.Company

                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling ConnectToTargetCompany()", sFuncName)
                    If ConnectToTargetCompany(oDIComp(j), oDtDistinct.Rows(j).Item(0).ToString, sErrdesc) <> RTN_SUCCESS Then
                        Throw New ArgumentException(sErrdesc)
                    End If

                    ' Get "business object" from Company object
                    oBP = oDIComp(j).GetBusinessObject(SAPbobsCOM.BoObjectTypes.oBusinessPartners)
                    oRs = oDIComp(j).GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting transaction on company database " & oDIComp(j).CompanyDB, sFuncName)
                    oDIComp(j).StartTransaction()

                    Dim DBRows() As DataRow = dv.Table.Select("F1='" & oDtDistinct.Rows(j).Item(0).ToString & "'")

                    For Each row As DataRow In DBRows
                        .CardCode = row.Item(1).ToString
                        sSQL = "select * from ocrd where ""CardType""='C' and ""CardCode""='" & .CardCode & "'"
                        oRs.DoQuery(sSQL)

                        If oRs.RecordCount > 0 Then
                            oBP.GetByKey(.CardCode)
                        End If

                        .CardName = row.Item(2).ToString
                        .ContactPerson = row.Item(3).ToString
                        .BillToAddress1 = row.Item(4).ToString
                        .BillToAddress2 = row.Item(5).ToString
                        .BillToAddress3 = row.Item(6).ToString
                        .BillToAddress4 = row.Item(7).ToString
                        .Country = row.Item(8).ToString
                        .ZipCode = row.Item(9).ToString
                        .Phone1 = row.Item(10).ToString
                        .Fax = row.Item(11).ToString
                        .EmailAddress = row.Item(12).ToString
                        .Website = row.Item(13).ToString
                        .TaxRegistration = row.Item(14).ToString
                        .PaymentTerm = row.Item(15).ToString
                        .SaleManCode = row.Item(16).ToString
                        .ARControlAccount = row.Item(17).ToString

                        Select Case row.Item(18).ToString
                            Case "GSTZR"
                                .TaxCode = "ZO"
                            Case "GSTNA"
                                .TaxCode = "NO"
                            Case "GSTOUT"
                                .TaxCode = "SO"
                        End Select

                        .CreditLimit = CDbl(row.Item(19))
                        .Discount = CDbl(row.Item(20))
                        .PaymentMethod = row.Item(21).ToString
                        .ShipAddress1 = row.Item(22).ToString
                        .ShipAddress2 = row.Item(23).ToString
                        .ShipAddress3 = row.Item(24).ToString
                        .ShipAddress4 = row.Item(25).ToString
                        .ShipCountry = row.Item(26).ToString
                        .ShipPostalCode = row.Item(27).ToString
                        .CurrencyCode = row.Item(28).ToString
                        .CustomerType = row.Item(29).ToString
                        .Remarks = row.Item(30).ToString

                        .BillToAddress = "BillToAddress1"
                        .ShipToAddress = "ShipToAddress1"

                        ' validations
                        sSQL = "select ""PymntGroup"" from OCTG"
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
                        dtPT = ExecuteSQLQueryForDT(sSQL, oDIComp(j).CompanyDB)

                        sSQL = "SELECT ""AcctCode"" FROM OACT"
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
                        dtAC = ExecuteSQLQueryForDT(sSQL, oDIComp(j).CompanyDB)

                        dtAC.DefaultView.RowFilter = "AcctCode = '" & .ARControlAccount & "'"
                        If dtAC.DefaultView.Count = 0 Then
                            isError = True
                            Call WriteToLogFile("AR Controla Account ::" & .ARControlAccount & " does not exist in SAP.", sFuncName)
                        End If

                        dtPT.DefaultView.RowFilter = "PymntGroup = '" & .PaymentTerm & "'"
                        If dtPT.DefaultView.Count = 0 Then
                            isError = True
                            Call WriteToLogFile("Payment group ::" & .PaymentTerm & " does not exist in SAP.", sFuncName)
                        End If


                        oBP.CardCode = .CardCode
                        oBP.CardName = .CardName
                        oBP.Phone1 = .Phone1
                        oBP.Fax = .Fax
                        oBP.EmailAddress = .EmailAddress
                        oBP.Website = .Website
                        'oBP.TaxExemptionLetterNum = .TaxRegistration
                        oBP.FederalTaxID = .TaxRegistration
                        oBP.DebitorAccount = .ARControlAccount
                        oBP.VatGroup = .TaxCode
                        oBP.CreditLimit = .CreditLimit
                        oBP.DiscountPercent = .Discount
                        oBP.Currency = .CurrencyCode
                        oBP.FreeText = .Remarks


                        'Payment Terms
                        Dim iPytmtgroupNum As Integer
                        iPytmtgroupNum = GetPytmtGroupNum(oDIComp(j), .PaymentTerm)
                        If iPytmtgroupNum <> 0 Then
                            oBP.PayTermsGrpCode = iPytmtgroupNum
                        End If

                        'Sales man code
                        Dim iSLPCode As Integer = -1
                        If Not .SaleManCode = String.Empty Then
                            CheckSalesMan(oDIComp(j), .SaleManCode, iSLPCode)
                        End If

                        oBP.SalesPersonCode = iSLPCode

                        'Bill to Address
                        Dim oRecordSetLoc As SAPbobsCOM.Recordset
                        oRecordSetLoc = (oDIComp(j).GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset))
                        sSQL = "select ""CardCode"",""AdresType"",""LineNum"" from CRD1 where ""CardCode""='" & .CardCode & "' "
                        sSQL &= " and ""AdresType""='B'"
                        sSQL &= " and ""Address""='" & .BillToAddress & "'"
                        oRecordSetLoc.DoQuery(sSQL)

                        If oRecordSetLoc.EoF Then
                            oBP.Addresses.Add()
                            oBP.Addresses.SetCurrentLine(0)
                        Else
                            For a As Integer = 0 To oBP.Addresses.Count - 1
                                oBP.Addresses.SetCurrentLine(a)
                                If oBP.Addresses.AddressName = .BillToAddress And oBP.Addresses.AddressType = SAPbobsCOM.BoAddressType.bo_BillTo Then
                                    Exit For
                                End If
                            Next
                        End If

                        oBP.Addresses.AddressType = SAPbobsCOM.BoAddressType.bo_BillTo
                        oBP.Addresses.AddressName = .BillToAddress
                        oBP.Addresses.Street = .BillToAddress1
                        oBP.Addresses.Block = .BillToAddress3
                        oBP.Addresses.City = .BillToAddress4
                        oBP.Addresses.StreetNo = .BillToAddress2
                        oBP.Addresses.ZipCode = .ZipCode
                        If Not GetCountryCode(oDIComp(j), .Country) = String.Empty Then
                            oBP.Addresses.Country = GetCountryCode(oDIComp(j), .Country)
                        End If

                        'Ship to Address
                        sSQL = "select ""CardCode"",""AdresType"",""LineNum"" from CRD1 where ""CardCode""='" & .CardCode & "' "
                        sSQL &= " and ""AdresType""='S'"
                        sSQL &= " and ""Address""='" & .ShipToAddress & "'"
                        oRecordSetLoc.DoQuery(sSQL)

                        If oRecordSetLoc.EoF Then
                            oBP.Addresses.Add()
                            oBP.Addresses.SetCurrentLine(1)
                        Else
                            For a As Integer = 0 To oBP.Addresses.Count - 1
                                oBP.Addresses.SetCurrentLine(a)
                                If oBP.Addresses.AddressName = .ShipToAddress Then
                                    Exit For
                                End If
                            Next
                        End If

                        oBP.Addresses.AddressType = SAPbobsCOM.BoAddressType.bo_ShipTo
                        oBP.Addresses.AddressName = .ShipToAddress
                        oBP.Addresses.Street = .ShipAddress1
                        oBP.Addresses.Block = .ShipAddress3
                        oBP.Addresses.City = .ShipAddress4
                        oBP.Addresses.StreetNo = .ShipAddress2
                        oBP.Addresses.ZipCode = .ShipPostalCode

                        If Not GetCountryCode(oDIComp(j), .ShipCountry) = String.Empty Then
                            oBP.Addresses.Country = GetCountryCode(oDIComp(j), .ShipCountry)
                        End If

                        If oRs.RecordCount > 0 Then
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("BP Code : " & .CardCode & " already exist. Call Update", sFuncName)
                            Console.WriteLine("Add/Update BP Code::" & .CardCode)
                            iRet = oBP.Update()
                        Else
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Call Add to insert BP Code : " & .CardCode, sFuncName)
                            oBP.CardType = SAPbobsCOM.BoCardTypes.cCustomer
                            Console.WriteLine("Add/Update BP Code::" & .CardCode)
                            iRet = oBP.Add()
                        End If

                        If iRet <> 0 Then
                            isError = True
                            oDIComp(j).GetLastError(iErr, sErr)
                            Call WriteToLogFile("Error in addition/updation for BP Code : " & .CardCode & "---" & sErr, sFuncName)
                            Exit Function
                        End If

                        Console.WriteLine("Succefully - Added/Updated BP Code::" & .CardCode)

                    Next
                Next

            End With
            For lCounter As Integer = 0 To UBound(oDIComp)
                If Not oDIComp(lCounter) Is Nothing Then
                    If oDIComp(lCounter).Connected = True Then
                        If oDIComp(lCounter).InTransaction = True Then
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Commit transaction on company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                            oDIComp(lCounter).EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
                        End If
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Disconnecting company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                        oDIComp(lCounter).Disconnect()
                        oDIComp(lCounter) = Nothing
                    End If
                End If
            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Function completed successfully.", sFuncName)
            AddUpdateCustomer = RTN_SUCCESS

        Catch ex As Exception

            For lCounter As Integer = 0 To UBound(oDIComp)
                If Not oDIComp(lCounter) Is Nothing Then
                    If oDIComp(lCounter).Connected = True Then
                        If oDIComp(lCounter).InTransaction = True Then
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Rollback transaction on company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                            oDIComp(lCounter).EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                        End If
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Disconnecting company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                        oDIComp(lCounter).Disconnect()
                        oDIComp(lCounter) = Nothing
                    End If
                End If
            Next

            sErrdesc = sErr & ex.Message
            isError = True
            If iRet = 0 Then p_oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error occured while updating the record", sFuncName)
            Call WriteToLogFile(sErrdesc, sFuncName)
        End Try
    End Function

    Private Function ProceessCustomer_SUB(ByVal sFileName As String, ByRef sErrdesc As String) As Long

        Dim sFuncName As String = "ProceessCustomer_SUB"
        Dim oDv As DataView = Nothing
        Dim oBP As SAPbobsCOM.BusinessPartners
        Dim lRetCode, lErrCode As Long
        Dim oDtDistinct As DataTable
        Dim oDIComp() As SAPbobsCOM.Company = Nothing

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function..", sFuncName)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling GetDataViewFromCSV()", sFuncName)
            oDv = GetDataViewFromCSV(sFileName)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("getting distinct DBs in the file.", sFuncName)
            oDtDistinct = oDv.Table.DefaultView.ToTable(True, "F1")

            ReDim oDIComp(oDtDistinct.Rows.Count)

            For j As Integer = 0 To oDtDistinct.Rows.Count - 1

                oDIComp(j) = New SAPbobsCOM.Company

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling ConnectToTargetCompany()", sFuncName)
                If ConnectToTargetCompany(oDIComp(j), oDtDistinct.Rows(j).Item(0).ToString, sErrdesc) <> RTN_SUCCESS Then
                    Throw New ArgumentException(sErrdesc)
                End If

                oBP = oDIComp(j).GetBusinessObject(SAPbobsCOM.BoObjectTypes.oBusinessPartners)

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting transaction on company database " & oDIComp(j).CompanyDB, sFuncName)
                oDIComp(j).StartTransaction()

                Dim DBRows() As DataRow = oDv.Table.Select("F1='" & oDtDistinct.Rows(j).Item(0).ToString & "'")

                For Each row As DataRow In DBRows
                    If oBP.GetByKey(row(2).ToString) = True Then
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Update BP ::" & row(2).ToString, sFuncName)
                        oBP.UserFields.Fields.Item("U_AI_ParentCo").Value = row(1).ToString
                        lRetCode = oBP.Update
                        If lRetCode <> 0 Then
                            oDIComp(j).GetLastError(lErrCode, sErrdesc)
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Update Customer SUB failed.", sFuncName)
                            Throw New ArgumentException(sErrdesc)
                        End If
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Successfully updated BP ::" & row(2).ToString, sFuncName)
                    Else
                        sErrdesc = "SubCompany Code ::" & row(2).ToString & "does not exists in SAP"
                        Throw New ArgumentException(sErrdesc)
                    End If
                Next
            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Commit all transaction..........", sFuncName)
            For lCounter As Integer = 0 To UBound(oDIComp)
                If Not oDIComp(lCounter) Is Nothing Then
                    If oDIComp(lCounter).Connected = True Then
                        If oDIComp(lCounter).InTransaction = True Then
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Commit transaction on company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                            oDIComp(lCounter).EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
                        End If
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Disconnecting company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                        oDIComp(lCounter).Disconnect()
                        oDIComp(lCounter) = Nothing
                    End If
                End If
            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Function completed successfully.", sFuncName)
            ProceessCustomer_SUB = RTN_SUCCESS

        Catch ex As Exception

            For lCounter As Integer = 0 To UBound(oDIComp)
                If Not oDIComp(lCounter) Is Nothing Then
                    If oDIComp(lCounter).Connected = True Then
                        If oDIComp(lCounter).InTransaction = True Then
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Rollback transaction on company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                            oDIComp(lCounter).EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                        End If
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Disconnecting company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                        oDIComp(lCounter).Disconnect()
                        oDIComp(lCounter) = Nothing
                    End If
                End If
            Next
            sErrdesc = ex.Message
            Call WriteToLogFile(sErrdesc, sFuncName)
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug(sErrdesc, sFuncName)
            ProceessCustomer_SUB = RTN_ERROR
           
        End Try
    End Function

    

End Module
